# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# 设置输出文件夹路径
output_folder = r'output\22'
os.makedirs(output_folder, exist_ok=True)

# 加载数据（请替换为实际文件路径）
file_path = r'data/22/女装店铺客户分类-原始数据.xlsx'

# 读取客户信息表
df_customers = pd.read_excel(file_path)

# 设置中文字体
font_path = r'fonts/SIMSUN.TTC'  # 请根据实际路径调整
font_prop = FontProperties(fname=font_path)

# 删除订单状态为“交易关闭”的行
df_filtered = df_customers[df_customers['订单状态'] != '交易关闭']

# 创建数据透视表统计每个买家会员名的购买频次
pivot_table_freq = pd.pivot_table(df_filtered, values='订单状态', index='买家会员名', aggfunc='count')
pivot_table_freq.columns = ['购买频次']

# 第一题：筛选出普通会员客户（购买频次 >= 1 且 <= 3）
ordinary_members = pivot_table_freq[(pivot_table_freq['购买频次'] >= 1) & (pivot_table_freq['购买频次'] <= 3)]
num_ordinary_members = len(ordinary_members)
print(f"网店的普通会员客户共有 {num_ordinary_members} 人。")

# 第二题：筛选出银会员客户（购买频次 > 3 且 <= 6），并保存结果
silver_members = pivot_table_freq[(pivot_table_freq['购买频次'] > 3) & (pivot_table_freq['购买频次'] <= 6)]

plt.figure(figsize=(14, 7))
silver_members.plot(kind='bar', legend=False)
plt.xlabel('买家会员名', fontproperties=font_prop)
plt.ylabel('购买频次', fontproperties=font_prop)
plt.title('银会员客户购买频次分析', fontproperties=font_prop)
plt.xticks(fontproperties=font_prop, rotation=45)
plt.tight_layout()

output_image_path_silver = os.path.join(output_folder, 'silver_members_distribution.png')
plt.savefig(output_image_path_silver, dpi=300, bbox_inches='tight')
plt.close()

output_excel_path_silver = os.path.join(output_folder, '银会员客户分析.xlsx')
with pd.ExcelWriter(output_excel_path_silver, engine='openpyxl') as writer:
    silver_members.to_excel(writer, sheet_name='银会员客户')

    workbook = writer.book
    worksheet = writer.sheets['银会员客户']
    
    img = Image(output_image_path_silver)
    worksheet.add_image(img, 'E2')

print("银会员客户分析已完成，结果已保存到", output_excel_path_silver)

# 第三题：筛选出金会员客户（购买频次 > 6 且 <= 10）
gold_members = pivot_table_freq[(pivot_table_freq['购买频次'] > 6) & (pivot_table_freq['购买频次'] <= 10)]
num_gold_members = len(gold_members)
print(f"金会员客户共有 {num_gold_members} 人。")

# 第四题：筛选出钻石会员客户（购买频次 > 10）
diamond_members = pivot_table_freq[pivot_table_freq['购买频次'] > 10]
diamond_member_names = diamond_members.index.tolist()
print(f"属于钻石会员客户的是：{', '.join(diamond_member_names)}。")

# 将所有结果保存到一个汇总Excel文件中
summary_excel_path = os.path.join(output_folder, '会员客户分析汇总.xlsx')
with pd.ExcelWriter(summary_excel_path, engine='openpyxl') as writer:
    ordinary_members.to_excel(writer, sheet_name='普通会员客户')
    silver_members.to_excel(writer, sheet_name='银会员客户')
    gold_members.to_excel(writer, sheet_name='金会员客户')
    diamond_members.to_excel(writer, sheet_name='钻石会员客户')

    workbook = writer.book
    summary_sheet = workbook.create_sheet(title='分析结论')
    summary_sheet.cell(row=1, column=1, value=f"网店的普通会员客户共有 {num_ordinary_members} 人。")
    summary_sheet.cell(row=2, column=1, value=f"金会员客户共有 {num_gold_members} 人。")
    summary_sheet.cell(row=3, column=1, value=f"属于钻石会员客户的是：{', '.join(diamond_member_names)}。")

print("所有分析已完成，结果已汇总保存到", summary_excel_path)